/**
 * Copyright 2011 Google Inc.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package com.google.api.adwords.starterkit.examples;

import com.google.api.adwords.starterkit.StarterKit;
import com.google.api.adwords.starterkit.settings.StarterKitSettings;
import com.google.api.adwords.starterkit.utils.DumpExceptions;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;

/**
 * The Class SQLUpdate is a demo application that uses a SQL query to update 
 * the keyword's maxCPC based on a specific criteria. You can drop the entire
 * StarterKit wrappers and do all updates you need using only the local DB
 * 
 * @author rodrigo.f.vale@gmail.com  (Rodrigo Vale)
 */
public class SQLUpdate {
  public static void howToUse() {
    System.out.print(
        "SYNOPSIS\n" +
        "      SQLUpdate [SETTINGS]\n" +
        "\n" +
        "DESCRIPTION\n" +
        "      Simple example that uses a SQL query to update the keywords'\n" +
        "maxCPC based on a specific criteria. You can drop the entire \n" +
        "framework if you want and just use the DB to update/create\n" +
        "keywords, etc.\n" +
        "\n" +
        "EXAMPLE\n" +
        "      java starterkit.jar SQLUpdate settings.xml\n");
    System.exit(0);
  }
  public static void main(String[] args) {
    if (args.length < 1) {
      howToUse();
    }
    StarterKitSettings starterKitSettings =  new StarterKitSettings(args[0]);
    StarterKit starterKit = new StarterKit(starterKitSettings);
    starterKit.load();

    Connection conn = starterKit.getConnection();
    Date date = new Date();
    long now = date.getTime();
    long oneWeek = 604800;

    PreparedStatement prep;
    try {
      prep = conn.prepareStatement(
          "UPDATE Keywords " +
          "SET maxcpc = (maxcpc * 1.1), " +
          "    lastUpdate = ?, " +
          "    IsUpdated = 1," +
          "    IsTouched = 1 " +
          "WHERE costPerConverstion < 12.00 " +
          "    AND averagePosition > 3 " +
          "    AND (? - lastUpdate) >  ?"
          );
      prep.setLong(1, now);
      prep.setLong(2, now);
      prep.setLong(3, oneWeek);
      prep.executeUpdate();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }        

    starterKit.save();
    starterKit.done();    
  }
}